Home > AJAX > AJAX Cascading Dropdown from Database

AJAX Cascading Dropdown from Database

January 3, 2009 Leave a comment Go to comments

Hi

try this example to use ajax CascadingDropDown to fill two dependent Dropdownlists

step1: Create webservice CascadingDataService.asmx

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
/// <summary>
/// Summary description for CascadingDataService
/// </summary>
[WebService(Namespace = "<a href="http://tempuri.org/">http://tempuri.org/</a>")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService()]

public class CascadingDataService : System.Web.Services.WebService {

    string conString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionString"].ToString();

    public CascadingDataService () {

        //Uncomment the following line if using designed components
        //InitializeComponent();
    }

    [WebMethod]
    public AjaxControlToolkit.CascadingDropDownNameValue[] GetDropDownCategories(string knownCategoryValues, string category)
    {
        SqlConnection sqlConn = new SqlConnection(conString);
        sqlConn.Open();
        SqlCommand sqlSelect = new SqlCommand("SELECT * FROM Categories", sqlConn);
        sqlSelect.CommandType = System.Data.CommandType.Text;
        SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlSelect);
        DataSet myDataset = new DataSet();
        sqlAdapter.Fill(myDataset);
        sqlConn.Close();

        List<AjaxControlToolkit.CascadingDropDownNameValue> cascadingValues = new List<AjaxControlToolkit.CascadingDropDownNameValue>();

        foreach (DataRow dRow in myDataset.Tables[0].Rows)
        {
            string categoryID = dRow["categoryID"].ToString();
            string categoryName = dRow["categoryName"].ToString();
            cascadingValues.Add(new AjaxControlToolkit.CascadingDropDownNameValue(categoryName,categoryID));
        }

        return cascadingValues.ToArray();
    }

    [WebMethod]
    public AjaxControlToolkit.CascadingDropDownNameValue[] GetDropDownProducts(string knownCategoryValues, string category)
    {
        int categoryID;

        StringDictionary categoryValues = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
       
        categoryID = Convert.ToInt32(categoryValues["category"]);

        SqlConnection sqlConn = new SqlConnection(conString);
        sqlConn.Open();
        SqlCommand sqlSelect = new SqlCommand("SELECT * FROM Products where categoryID = @categoryID", sqlConn);
        sqlSelect.CommandType = System.Data.CommandType.Text;
        sqlSelect.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
        SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlSelect);
        DataSet myDataset = new DataSet();
        sqlAdapter.Fill(myDataset);
        sqlConn.Close();

        List<AjaxControlToolkit.CascadingDropDownNameValue> cascadingValues = new List<AjaxControlToolkit.CascadingDropDownNameValue>();

        foreach (DataRow dRow in myDataset.Tables[0].Rows)
        {
            string productID = dRow["productID"].ToString();
            string productName = dRow["productName"].ToString();
            cascadingValues.Add(new AjaxControlToolkit.CascadingDropDownNameValue(productName, productID));
        }

        return cascadingValues.ToArray();
    }
}

step2: add new web page

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="ajax-cascading-dropdown-database.aspx.cs" Inherits="ajax_data_ajax_cascading_dropdown_database" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>">

<html xmlns="<a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>" >
<head id="Head1" runat="server">
    <title>AJAX Cascading DropDown using Database</title>
    <style type="text/css">
   
    body {
    font-family: verdana;
    font-size: 11px;
    }
   
    </style>
   
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
    </div>
        <table border="0" cellpadding="2" cellspacing="0">
            <tr>
                <td>
                    Select Category:</td>
                <td>
                    <asp: DropDownList ID="drdCategory" runat="server">
                    </asp: DropDownList>
                    <ajaxToolkit:CascadingDropDown
                    ID="CascadingDropDown1"
                    runat="server"
                    Category="category"
                    TargetControlID="drdCategory"
                    PromptText="[Select Category]"
                    LoadingText="Loading categories..."
                    ServicePath="cascadingdataservice.asmx"
                    ServiceMethod="GetDropDownCategories">
                    </ajaxToolkit:CascadingDropDown>
                </td>
            </tr>
            <tr>
                <td>
                    Select Product:</td>
                <td>
                    <asp: DropDownList ID="drdProduct" runat="server" OnSelectedIndexChanged="drdProduct_SelectedIndexChanged" AutoPostBack="True">
                    </asp: DropDownList>
                    <ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server"
                    Category="product"
                    TargetControlID="drdProduct"
                    ParentControlID="drdCategory"
                    PromptText="[Select Product]"
                    LoadingText="Loading products..."
                    ServicePath="cascadingdataservice.asmx"
                    ServiceMethod="GetDropDownProducts">
                    </ajaxToolkit:CascadingDropDown>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    &nbsp;</td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                        <ContentTemplate>
                            <asp:Label ID="Label1" runat="server"></asp:Label>
                        </ContentTemplate>
                        <Triggers>
                            <asp:AsyncPostBackTrigger ControlID="drdProduct" EventName="SelectedIndexChanged" />
                        </Triggers>
                    </asp:UpdatePanel>
                </td>
            </tr>
            <tr>
                <td colspan="2">                   
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

in code behind :

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class ajax_data_ajax_cascading_dropdown_database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void drdProduct_SelectedIndexChanged(object sender, EventArgs e)
    {
        Label1.Text = string.Format("You selected <b>{0}</b> from <b>{1}</b> category.", drdProduct.SelectedItem.Text, drdCategory.SelectedItem.Text);
    }
}

Hope it helps ….

Best Regards

YZ

Categories: AJAX Tags: ,
  1. fenzly
    March 17, 2009 at 5:50 am
    Reply

    what is StringDictionary mean?

    why it cannot be found i the web method?

  2. Daniel Gumucio
    May 28, 2009 at 10:35 pm
    Reply

    It’s an specialized collection.
    Add:
    using System.Collections.Specialized;

  3. yasserzaid
    May 29, 2009 at 11:15 am
    Reply

    Thanks Daniel for your comment but if you look to my code again you will find i add
    using System.Collections.Specialized;

    Good Luck

  4. Salayem
    May 29, 2010 at 1:06 pm
    Reply

    i try to do it but i face problem

    ” could not create type “WebService”

    please help

    thank you

  5. Salayem
    May 30, 2010 at 8:49 am
    Reply

    i fixed the problem

    but the problem i faced now

    that it is reading any thing from data base..

    and the connection is correct

  6. yasserzaid
    June 20, 2010 at 3:57 pm
    Reply

    @Salayem :- can you tell me what the problem? did you get any Error message

  1. No trackbacks yet.

Leave a comment